-- 创建新用户
CREATE USER lcqh WITH PASSWORD '479368';

-- =============================================
-- 1. 创建数据库（如果不存在）
-- =============================================
-- 删除数据库
DROP DATABASE IF EXISTS company_db;
CREATE DATABASE company_db
    WITH
    OWNER = lcqh                -- 数据库所有者
    ENCODING = 'UTF8'           -- 字符编码
    LC_COLLATE = 'en_US.UTF-8'  -- 排序规则
    LC_CTYPE = 'en_US.UTF-8'    -- 字符分类
    CONNECTION LIMIT = -1;      -- 连接数限制, -1表示无限制

-- 授权新用户
GRANT ALL PRIVILEGES ON DATABASE company_db TO lcqh;

-- =============================================
-- 2. 连接到数据库
-- =============================================
\c company_db;

-- =============================================
-- 3. 创建表结构（含约束）
-- =============================================
-- 员工表
CREATE TABLE IF NOT EXISTS employees (
    employee_id SERIAL PRIMARY KEY,     -- 员工编号, 自增主键
    first_name VARCHAR(50) NOT NULL,    -- 姓氏
    last_name VARCHAR(50) NOT NULL,     -- 名称
    email VARCHAR(100) UNIQUE NOT NULL, -- 邮箱地址
    hire_date DATE NOT NULL CHECK (hire_date <= CURRENT_DATE),
    salary NUMERIC(10,2) CHECK (salary >= 0),
    department_id INT
);
COMMENT ON COLUMN employees.employee_id IS '员工编号';
COMMENT ON COLUMN employees.first_name IS '姓氏';
COMMENT ON COLUMN employees.last_name IS '名称';
COMMENT ON COLUMN employees.email IS '邮箱地址';
COMMENT ON COLUMN employees.hire_date IS '雇佣时间';
COMMENT ON COLUMN employees.salary IS '薪水';
COMMENT ON COLUMN employees.department_id IS '部门编号';
COMMENT ON TABLE employees IS '员工表';

-- 部门表
CREATE TABLE IF NOT EXISTS departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    manager_id INT REFERENCES employees(employee_id)
);
COMMENT ON COLUMN departments.department_id IS '部门编号';
COMMENT ON COLUMN departments.department_name IS '部门名称';
COMMENT ON COLUMN departments.manager_id IS '管理者编号';
COMMENT ON TABLE departments IS '部门表';


-- 订单表（分布式表）
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,       -- 订单ID（全局唯一）
    user_id BIGINT NOT NULL,              -- 用户ID（分片键）
    product_id BIGINT NOT NULL,           -- 商品ID
    amount NUMERIC(10, 2) NOT NULL,       -- 订单金额
    status VARCHAR(20) NOT NULL,          -- 状态（如 paid, pending, cancelled）
    created_at TIMESTAMP DEFAULT NOW(),   -- 创建时间
    updated_at TIMESTAMP DEFAULT NOW()    -- 更新时间
);

-- 添加外键约束
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE SET NULL;

-- =============================================
-- 4. 创建索引（优化查询性能）
-- =============================================
CREATE INDEX idx_employees_last_name ON employees(last_name);
CREATE INDEX idx_departments_name ON departments(department_name);

-- =============================================
-- 5. 插入示例数据
-- =============================================
-- 插入部门数据
INSERT INTO departments (department_name) VALUES
    ('Human Resources'),
    ('Engineering'),
    ('Sales')
RETURNING *;

-- 插入员工数据
INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id)
VALUES
    ('John', 'Doe', 'john.doe@company.com', '2023-01-15', 75000.00, 2),
    ('Jane', 'Smith', 'jane.smith@company.com', '2023-03-22', 68000.00, 1),
    ('Bob', 'Johnson', 'bob.johnson@company.com', '2023-06-10', 82000.00, 3);

-- =============================================
-- 6. 创建视图（简化复杂查询）
-- =============================================
CREATE OR REPLACE VIEW active_employees AS
SELECT
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS full_name,
    d.department_name,
    e.hire_date
FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE e.hire_date >= '2023-01-01';

-- =============================================
-- 7. 创建存储过程
-- =============================================
-- 按部门统计平均薪资
CREATE OR REPLACE FUNCTION get_avg_salary_by_dept()
    RETURNS TABLE (department_name VARCHAR(100), avg_salary NUMERIC) AS $$
BEGIN
    RETURN QUERY
        SELECT d.department_name, AVG(e.salary)::NUMERIC(10,2)
        FROM departments d
        LEFT JOIN employees e
        ON d.department_id = e.department_id
        GROUP BY d.department_name;
END;
$$ LANGUAGE plpgsql;

-- =============================================
-- 8. 创建触发器（自动更新时间戳）
-- =============================================
-- 添加修改时间字段
ALTER TABLE employees ADD COLUMN modified_at TIMESTAMP;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_modified_at()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到employees表
CREATE TRIGGER trg_employees_update
    BEFORE UPDATE ON employees
    FOR EACH ROW
EXECUTE FUNCTION update_modified_at();

-- =============================================
-- 9. 验证数据
-- =============================================
-- 查询所有员工
SELECT * FROM employees;

-- 查询视图
SELECT * FROM active_employees;

-- 调用存储过程
SELECT * FROM get_avg_salary_by_dept();

-- =============================================
-- 10. 清理脚本（示例，慎用！）
-- =============================================
-- DROP DATABASE IF EXISTS company_db;